#!user/bin/python3
# -*- coding:UTF-8 -*-

################################################################################
#
# Copyright (c) 2020 openEuler.org, Inc. All Rights Reserved
#
################################################################################
"""
Interaction between file and mysql data
Authors: xiaojianghui
Date:    12/08/2020 11:01 AM
"""

import os
import xlrd
import time
import urllib.parse
from sqlalchemy import Column, String, create_engine, Integer, Boolean
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from downloadtask import downloadfiletask
from dbConnecttion import Config

Base = declarative_base()


class Details(Base):
    """
        sqlalchemy orm
    """
    __tablename__ = 'cve_spec_error'
    id = Column(Integer, primary_key=True)
    cve_num = Column(String)
    cve_desc = Column(String)
    cve_owner = Column(String)
    cve_status = Column(Boolean)
    pack_name = Column(String)
    create_time = Column(String)
    update_time = Column(String)
    delete_time = Column(String)


def cur_date():
    """
        current date
    Returns:createTime:string

    """
    create_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
    return create_time


def parse_error_excels(file_name):
    """
        parse spec_error_excels files
    Returns:

    """
    cve_num_list = []
    cve_desc_list = []
    cve_owner_list = []
    pack_name_list = []
    list_all = []
    try:
        data = xlrd.open_workbook('./spec_error_excels/' + file_name)
        table_one = data.sheet_by_name("Sheet1")
        row_number = table_one.nrows
        for i in range(1, row_number):
            if table_one.cell(i, 1).value:
                cve_num_list.append(table_one.cell(i, 1).value)
            else:
                continue
            if table_one.cell(i, 4).value:
                cve_desc_list.append(table_one.cell(i, 4).value)
            else:
                cve_desc_list.append("")
            if table_one.cell(i, 2).value:
                cve_owner_list.append(table_one.cell(i, 2).value)
            else:
                cve_owner_list.append("")
            if table_one.cell(i, 3).value:
                pack_name_list.append(table_one.cell(i, 3).value)
            else:
                pack_name_list.append("")
        list_all = [cve_num_list, cve_desc_list, cve_owner_list, pack_name_list]
    except IndexError as e:
        print("Subscript out of bounds", e)
    except xlrd.XLRDError as e:
        print("Form not found：Sheet1", e)
    return list_all


def add_error_details():
    """
    Conditions for filtering incorrect data
    Returns:

    """
    downloadfiletask.download_spec_error_excels()
    files = os.listdir('./spec_error_excels')
    for file in files:
        result = parse_error_excels(file)
        if not result[0]:
            continue
        print('开始批量插入人工CVE过滤条件')
        objects = []
        pwd = urllib.parse.quote_plus(Config.DBPWD)
        engine = create_engine(
            "mysql://" + Config.DBUSER + ":" + pwd + "@" + Config.DBHOST + "/" + Config.DBNAME + "?charset=utf8")
        for i in range(0, len(result[0])):
            objects.append(Details(cve_num=result[0][i], cve_desc=result[1][i], cve_owner=result[2][i],
                                   cve_status=1, pack_name=result[3][i], create_time=cur_date()))
        db_session = sessionmaker(bind=engine)
        session = db_session()
        try:
            session.bulk_save_objects(objects)
        except IntegrityError:
            session.rollback()
        session.commit()
        session.close()
        os.remove('./spec_error_excels/' + file)
